package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.util.Pager;
import SiTuQingYou.vo.PackCar;
import SiTuQingYou.vo.PackageTour;
import SiTuQingYou.vo.User;

public class PackCarDao 
{
	private  PackCarDao() {}
	private static  PackCarDao pcd= new  PackCarDao();
	public static  PackCarDao getpcd()
	{
		return pcd;
	}
	
	//根据用户id查询出购物车内容
	public ArrayList<PackCar> lookForAllById(int userid,Pager pager)
	{
		pager.setDataCount(this.getShoppingCartCount(userid));
		
		ArrayList<PackCar> allsc = new ArrayList<PackCar>();
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "select * from packcar,packagetour where userid=? and packcar.fakedelete=0 and packagetour.fakedelete=0 and "
					+ "packagetourid=packagetour.id ORDER BY packcar.id limit ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			ps.setInt(2, pager.getFirstRow());//获得limit后面第一个数是几
			ps.setInt(3, pager.getPageSize());//获得每页几条记录
			rs = ps.executeQuery();
			
			while(rs.next()) {
				
				PackCar packcar = new PackCar();
				packcar.setId(rs.getInt("packcar.id"));
				packcar.setPacktime(rs.getTimestamp("packtime"));//购物车的时间
				User user = new User();
				user.setId(rs.getInt("userid"));//对应的用户id
				packcar.setUser(user);
				PackageTour pt = new PackageTour();
				pt.setPhoto(rs.getString("packagetour.photo"));//跟团游照片
				pt.setTitle(rs.getString("packagetour.title"));//跟团游主题
				pt.setPacktime(rs.getTimestamp("packagetour.packtime"));//跟团游的团期
				pt.setPeople(rs.getInt("packagetour.people"));//跟团游的总人数
				pt.setPirce(rs.getFloat("packagetour.price"));//跟团游价格
				packcar.setPackagetour(pt);
				
				allsc.add(packcar);
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allsc;
		
	}
	
	//获得此用户购物车内总数
	public int getShoppingCartCount(int userid)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; 
		int count =0;
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql="select count(id) as sccount from packcar where userid=? and fakedelete=0";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,userid);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt("sccount");
			}
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
		    	DBHelper.getDB().close(rs, ps, conn);
		}
		return count;
		
	}
	
	
	//删除选中
	public void deleteAllChecked(String cid[]) {
		String s = "";
		for(String id:cid)
		{
			s = s+","+id;
		}
		s = s.substring(1);
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		
		try 
		{
			conn = DBHelper.getDB().getConn();
			String sql = "update packcar set fakedelete=1 where id in("+s+")";
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBHelper.getDB().close(rs, ps, conn);
		}
	}
	
	
}
